<?php
class Receive_paid extends Admin_Controller
{
	
	function __construct()
	{
		parent::__construct();
		$this->load->model('fund_model','fund');
		$this->load->model('voucher_model','voucher');
		$this->load->model('voucher_balance_model','balance');
		$this->load->model('voucher_check_model','check');
		$this->load->model('voucher_receive_paid_model','receive_paid');
		$this->load->model('voucher_detail_model','voucher_detail');
	}
	
	//**** รายงานรับจ่าย ******
	function index($fund_id)
	{
		$cash_id = $this->db->getOne('select id from accounts where money = 1 and fund_id = '.$fund_id);
		$bank_id = $this->db->getOne('select id from accounts where money = 2 and fund_id = '.$fund_id);
		//$this->db->debug = TRUE;
		$data['fund'] = $this->fund->get_row($fund_id);
		$user = login_data();
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];
		$data['agency_id'] = $agency_id;
		//**************Start where section ******************
		$where = '';
		list($year,$month,$data['year'],$data['month']) = last_voucher_date($fund_id);
		
		$where = ' and YEAR(vouchers.voucher_date) = '.$year.' and MONTH(vouchers.voucher_date) = '.$month;
		
		list($start_date,$end_date) = fix_budget($year,$month);
		$summary_where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		
		$summary_balance_where = ' and YEAR(vouchers.voucher_date) = YEAR(\''.$start_date.'\') and MONTH(vouchers.voucher_date) = MONTH(\''.$start_date.'\')';

		$balance_where = ' and YEAR(vouchers.voucher_date) = '.$year.' and MONTH(vouchers.voucher_date) = '.$month;
		
		if(!empty($agency_id))
		{
			$where .= ' and vouchers.agency_id = '.$agency_id;
			$balance_where .= ' and vouchers.agency_id = '.$agency_id;
			$summary_where .= ' and vouchers.agency_id = '.$agency_id;
			$summary_balance_where .= ' and vouchers.agency_id = '.$agency_id;
		}
		
		$cash = $this->db->getrow('select * from voucher_details 
		where account_id = '.$cash_id.'
		and voucher_id = (select id from vouchers where is_balance = 1 and mode is null  and fund_id = '.$fund_id.' '.$balance_where.')');
		
		$deposits = $this->db->getrow('select * from voucher_details 
		where account_id = '.$bank_id.'
		and voucher_id = (select id from vouchers where is_balance = 1 and mode is null  and fund_id = '.$fund_id.' '.$balance_where.')');
		
		$summary_cash = $this->db->getrow('select * from voucher_details 
		where account_id = '.$cash_id.'
		and voucher_id = (select id from vouchers where is_balance = 1 and mode is null  and fund_id = '.$fund_id.' '.$summary_balance_where.')');
		
		$summary_deposits = $this->db->getrow('select * from voucher_details 
		where account_id = '.$bank_id.'
		and voucher_id = (select id from vouchers where is_balance = 1 and mode is null  and fund_id = '.$fund_id.' '.$summary_balance_where.')');
		
		$data['balance_cash'] = $this->db->getone('
		select 
		(COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) price
		from accounts
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' group by account_id) a
		on a.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' group by account_id) b
		on b.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' group by account_id) c
		on c.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' group by account_id) d
		on d.account_id = accounts.id
		where fund_id = '.$fund_id.'
		and accounts.id = '.$cash_id.'
		',FALSE);
		
		
		$data['balance_deposits'] = $this->db->getone('
		select 
		(COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) price
		from accounts
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' group by account_id) a
		on a.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' group by account_id) b
		on b.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' group by account_id) c
		on c.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' group by account_id) d
		on d.account_id = accounts.id
		where fund_id = '.$fund_id.'
		and accounts.id = '.$bank_id.'
		',FALSE);
		
		if($cash)
		{
			$data['cash'] = $cash['PRICE'];
			if($cash['TYPE']=='cr')$data['cash'] = $data['cash'] * -1;
		}
		else
		{
			$data['cash'] = 0;
		}
		
		if($deposits)
		{
			$data['deposits'] = $deposits['PRICE'];
			if($deposits['TYPE']=='cr')$data['deposits'] = $data['deposits'] * -1;
		}
		else
		{
			$data['deposits'] = 0;
		}
		
		if($summary_cash)
		{
			$data['summary_cash'] = $summary_cash['PRICE'];
			if($summary_cash['TYPE']=='cr')$data['summary_cash'] = $data['summary_cash'] * -1;
		}
		else
		{
			$data['summary_cash'] = 0;
		}
		
		if($summary_deposits)
		{
			$data['summary_deposits'] = $summary_deposits['PRICE'];
			if($summary_deposits['TYPE']=='cr')$data['summary_deposits'] = $data['summary_deposits'] * -1;
		}
		else
		{
			$data['summary_deposits'] = 0;
		}
		
		
	
		$data['result'] = $this->voucher_detail->get('
		select a.account_id,a.account_name,COALESCE(b.type,c.type) type,COALESCE(b.real_price,0) real_price,COALESCE(c.real_price2,0) real_price2 from 
		(select distinct account_id,accounts.name account_name from voucher_details left join vouchers on voucher_details.voucher_id = vouchers.id left join accounts
		on voucher_details.account_id = accounts.id  where vouchers.fund_id = 1
		and vouchers.is_balance is null
		and vouchers.mode is null 
		and voucher_details.account_id not in ('.$cash_id.','.$bank_id.')
		and vouchers.voucher_type_id <> 3 and voucher_details.voucher_id in (select voucher_id from voucher_details where account_id in ('.$cash_id.','.$bank_id.'))'.$summary_where.') a
		left join (
		select account_id, account_name,type,sum(real_price) real_price from (
select voucher_details.*,vouchers.voucher_date,vouchers.number,accounts.name account_name,
		(
		CASE when voucher_details.price <> (select sum(price) from voucher_details a where a.voucher_id = voucher_details.voucher_id and a."TYPE" <> voucher_details."TYPE") 
		THEN voucher_details.price 
		ELSE (select price from voucher_details a where a.account_id = '.$cash_id.' and  a.voucher_id = voucher_details.voucher_id and a."TYPE" <> voucher_details."TYPE") 
		END
		)AS real_price
		from voucher_details 
		left join vouchers 
		on voucher_details.voucher_id = vouchers.id 
		left join accounts
		on voucher_details.account_id = accounts.id 
		where  
		vouchers.fund_id = '.$fund_id.'
		and vouchers.is_balance is null
		and vouchers.mode is null 
		and vouchers.voucher_type_id <> 3
		and voucher_details.voucher_id in (select voucher_id from voucher_details where account_id = '.$cash_id.')
		and voucher_details.account_id <> '.$cash_id.'
		'.$where.'
		and voucher_details."TYPE" <> (select "TYPE" from voucher_details a where a.account_id = '.$cash_id.' and a.voucher_id = voucher_details.voucher_id)
union
select voucher_details.*,vouchers.voucher_date,vouchers.number,accounts.name account_name,
		(
		CASE when voucher_details.price <> (select sum(price) from voucher_details a where a.voucher_id = voucher_details.voucher_id and a."TYPE" <> voucher_details."TYPE") 
		THEN voucher_details.price 
		ELSE (select price from voucher_details a where a.account_id = '.$bank_id.' and  a.voucher_id = voucher_details.voucher_id and a."TYPE" <> voucher_details."TYPE") 
		END
		)AS real_price
		from voucher_details 
		left join vouchers 
		on voucher_details.voucher_id = vouchers.id 
		left join accounts
		on voucher_details.account_id = accounts.id 
		where  
		vouchers.fund_id = '.$fund_id.'
		and vouchers.is_balance is null
		and vouchers.mode is null 
		and vouchers.voucher_type_id <> 3
		and voucher_details.voucher_id in (select voucher_id from voucher_details where account_id = '.$bank_id.')
		and voucher_details.account_id <> '.$bank_id.'
		'.$where.'
		and voucher_details."TYPE" <> (select "TYPE" from voucher_details a where a.account_id = '.$bank_id.' and a.voucher_id = voucher_details.voucher_id)
)a
group by account_id,account_name,type

) b
on b.account_id = a.account_id
left join (
		select account_id, account_name,type,sum(real_price) real_price2 from (
select voucher_details.*,vouchers.voucher_date,vouchers.number,accounts.name account_name,
		(
		CASE when voucher_details.price <> (select sum(price) from voucher_details a where a.voucher_id = voucher_details.voucher_id and a."TYPE" <> voucher_details."TYPE") 
		THEN voucher_details.price 
		ELSE (select price from voucher_details a where a.account_id = '.$cash_id.' and  a.voucher_id = voucher_details.voucher_id and a."TYPE" <> voucher_details."TYPE") 
		END
		)AS real_price
		from voucher_details 
		left join vouchers 
		on voucher_details.voucher_id = vouchers.id 
		left join accounts
		on voucher_details.account_id = accounts.id 
		where  
		vouchers.fund_id = '.$fund_id.'
		and vouchers.is_balance is null
		and vouchers.mode is null 
		and vouchers.voucher_type_id <> 3
		and voucher_details.voucher_id in (select voucher_id from voucher_details where account_id = '.$cash_id.')
		and voucher_details.account_id <> '.$cash_id.'
		'.$summary_where.'
		and voucher_details."TYPE" <> (select "TYPE" from voucher_details a where a.account_id = '.$cash_id.' and a.voucher_id = voucher_details.voucher_id)
union
select voucher_details.*,vouchers.voucher_date,vouchers.number,accounts.name account_name,
		(
		CASE when voucher_details.price <> (select sum(price) from voucher_details a where a.voucher_id = voucher_details.voucher_id and a."TYPE" <> voucher_details."TYPE") 
		THEN voucher_details.price 
		ELSE (select price from voucher_details a where a.account_id = '.$bank_id.' and  a.voucher_id = voucher_details.voucher_id and a."TYPE" <> voucher_details."TYPE") 
		END
		)AS real_price
		from voucher_details 
		left join vouchers 
		on voucher_details.voucher_id = vouchers.id 
		left join accounts
		on voucher_details.account_id = accounts.id 
		where  
		vouchers.fund_id = '.$fund_id.'
		and vouchers.is_balance is null
		and vouchers.mode is null 
		and vouchers.voucher_type_id <> 3
		and voucher_details.voucher_id in (select voucher_id from voucher_details where account_id = '.$bank_id.')
		and voucher_details.account_id <> '.$bank_id.'
		'.$summary_where.'
		and voucher_details."TYPE" <> (select "TYPE" from voucher_details a where a.account_id = '.$bank_id.' and a.voucher_id = voucher_details.voucher_id)
)a
group by account_id,account_name,type

) c
on c.account_id = a.account_id
where b.real_price is not null or c.real_price2 is not null
order by type asc,account_id asc
		',FALSE);
		$this->template->append_metadata(js_print());
		$this->template->build('voucher_receive_paid',$data);
	}
	
	
	
}
?>